clear

global cbp_vars  testab cty* lb ub 
*global nlrb // *RC *RD
global agg year countyid 
global final_agg year $area $ind


///////////////////
// CBP - 6 digit
// Naics 2007 -from: 2008 - 2011
//////////////////

tempfile temp
use ${wd}/cbp/CBP_Panel.dta if inrange(year,2008,2011), clear

count
rename naics naics07
tostring naics07, replace

egen tsum = sum(testab)
joinby naics07 using "${raw}/cbp/full_naics02_naics07.dta" 

egen wt = sum(wt_mappings), by(year countyid naics07 state2)
bysort year countyid naics07: replace wt = wt_mappings/wt

gcollapse (sum) $cbp_vars  tsum  [iw=wt], by($agg naics02 state2)

joinby naics02 using "${raw}/cbp/full_naics97_naics02.dta" ,  

egen wt = sum(wt_mappings), by(year countyid naics02)
bysort year countyid naics02: replace wt = wt_mappings/wt


gcollapse (sum) $cbp_vars   tsum  [iw=wt], by($agg naics97 state2)


joinby naics97 using "${raw}/cbp/full_sic87_naics97.dta" ,  

gen code = sic87
destring code, force replace
drop if code == .

egen wt = sum(weight_hybrid), by(year countyid naics97 state2)
bysort year countyid naics97: replace wt = weight_hybrid/wt


gcollapse (sum) $cbp_vars  tsum  [iw=wt], by($agg sic87 state2)


save ${wd}/cbp/part1.dta, replace


//////////////////
// Naics 2002 -from: 2003 - 2007 
//////////////////

tempfile temp
use ${wd}/cbp/CBP_Panel.dta if inrange(year,2003,2007), clear

count
rename naics naics02
tostring naics02, replace

egen tsum = sum(testab)
sum tsum
local total = r(mean)

joinby naics02 using "${raw}/cbp/full_naics97_naics02.dta" ,  

egen wt = sum(wt_mappings), by(year countyid naics02)
bysort year countyid naics02: replace wt = wt_mappings/wt


gcollapse (sum) $cbp_vars  tsum  [iw=wt], by($agg naics97 state2)


joinby naics97 using "${raw}/cbp/full_sic87_naics97.dta" , 

gen code = sic87
destring code, force replace
drop if code == . 

egen wt = sum(weight_hybrid), by(year countyid naics97)
bysort year countyid naics97: replace wt = weight_hybrid/wt


gcollapse (sum) $cbp_vars  tsum  [iw=wt], by($agg sic87 state2)

sum testab
return list
di `total'


save ${wd}/cbp/part2.dta, replace


//////////////////
// Naics 1997 -from: 1998 - 2002
//////////////////

tempfile temp
use ${wd}/cbp/CBP_Panel.dta if inrange(year,1998,2002), clear

count
rename naics naics97
tostring naics97, replace

egen tsum = sum(testab)
sum tsum
local total = r(mean)


joinby naics97 using "${raw}/cbp/full_sic87_naics97.dta" ,  

gen code = sic87
destring code, force replace
drop if code == .

egen wt = sum(weight_hybrid), by(year countyid naics97)
bysort year countyid naics97: replace wt = weight_hybrid/wt


gcollapse (sum) $cbp_vars   [iw=wt], by($agg sic87 state2)

sum testab
return list
di `total'



save ${wd}/cbp/part3.dta, replace

// CBP - 4 digit
// Sic 87 -from: 1988  - 1997


tempfile temp
use ${wd}/cbp/CBP_Panel.dta if inrange(year,1988,1997), clear

*rename naics sic87
gen sic87 = string(naics,"%04.0f")


save ${wd}/cbp/part4.dta, replace

// Sic 77 -from: 1977 - 1987

tempfile temp 
use ${wd}/cbp/CBP_Panel.dta if inrange(year,1977,1987), clear

gen sic77 = string(naics,"%04.0f")

joinby sic77 using "${raw}/cbp/full_sic77_sic87.dta" ,  

egen wt = sum(weight_mappings), by(year countyid sic77)
bysort year countyid sic77: replace wt = weight_mappings/wt

gcollapse (sum) $cbp_vars     [iw=wt], by($agg sic87 state2)

save ${wd}/cbp/part5.dta, replace


use ${wd}/cbp/part1.dta, clear
append using ${wd}/cbp/part2.dta
append using ${wd}/cbp/part3.dta
append using ${wd}/cbp/part4.dta
append using ${wd}/cbp/part5.dta


save ${wd}/cbp/cbp_countyid_sic87.dta, replace


use ${wd}/cbp/cbp_countyid_sic87.dta, clear

// Convert 3 digit sic to 3
gen sic3 = sic87
replace sic3 = substr(sic3, 1,3)
destring sic3, replace

count
joinby sic3 using "${nlrb}/ddorn_agg_sic_cic.dta"
count

do "${cbp}/sic3_detind.do"

gcollapse(sum) $cbp_vars, by(year countyid detind state2 )

save ${wd}/cbp/cbp_countyid_detind.dta, replace


use ${wd}/cbp/cbp_countyid_detind.dta, clear



// Create city defs
g city=.
preserve
	keep if year<=1980
	do "${nlrb}/county_city1973_1980"
	save ${temp}/part1.dta,replace
restore
preserve
	keep if year>1980 & year<=1989
	do "${nlrb}/county_city1981_1989"
	save ${temp}/part2.dta,replace
restore
preserve
	keep if year>1989 & year<=1992
	do "${nlrb}/county_city1990_1992"
	save ${temp}/part3.dta,replace
restore
preserve
	keep if year>1992 & year<=2003
	do "${nlrb}/county_city1993_plus"
	*do "/Users/bensand/Library/CloudStorage/Dropbox/Projects/Union_Project_Shared/Programs/nlrb/programs/county_city1993_plus.do"
	save ${temp}/part4.dta,replace
restore
preserve
	keep if year>2003
	do "${nlrb}/county_city2004_plus"
	save ${temp}/part5.dta,replace
restore

clear
append using ${temp}/part1.dta ${temp}/part2.dta ${temp}/part3.dta ${temp}/part4.dta ///
	${temp}/part5.dta
	
// Dallas/Fort worth fix	
replace city=17 if city==44	

// Now create proper city definitions here which are consistent across data sets	
	
do "${nlrb}/labels_city_final"	
do "${nlrb}/labels_state"	
	
g city_plus=city
g city_plus2=city

// City Defs
replace city_plus=	58	if state2==	12	& city==.
replace city_plus=	59	if state2==	14	& city==.
replace city_plus=	60	if state2==	21	& city==.
replace city_plus=	61	if state2==	22	& city==.
replace city_plus=	62	if state2==	23	& city==.
replace city_plus=	63	if state2==	31  & city==.
replace city_plus=	64	if state2==	32	& city==.
replace city_plus=	65	if state2==	33	& city==.
replace city_plus=	66	if state2==	34	& city==.
replace city_plus=	67	if state2==	35	& city==.
replace city_plus=	68	if state2==	41	& city==.
replace city_plus=	69	if state2==	43	& city==.
replace city_plus=	70	if state2==	47	& city==.
replace city_plus=	71	if state2==	52	& city==.
replace city_plus=	72	if state2==	53	& city==.
replace city_plus=	73	if state2==	54	& city==.
replace city_plus=	74	if state2==	56	& city==.
replace city_plus=	75	if state2==	58	& city==.
replace city_plus=	76	if state2==	59	& city==.
replace city_plus=	77	if state2==	61	& city==.
replace city_plus=	78	if state2==	63	& city==.
replace city_plus=	79	if state2==	72	& city==.
replace city_plus=	80	if state2==	74	& city==.
replace city_plus=	81	if state2==	84	& city==.
replace city_plus=	82	if state2==	91	& city==.
replace city_plus=	83	if state2==	92	& city==.
replace city_plus=	84	if state2==	93	& city==.


replace city_plus2=	58	if state2==	11	& city==.
replace city_plus2=	59	if state2==	12	& city==.
replace city_plus2=	60	if state2==	13	& city==.
replace city_plus2=	61	if state2==	14	& city==.
replace city_plus2=	62	if state2==	15	& city==.
replace city_plus2=	63	if state2==	16	& city==.
replace city_plus2=	64	if state2==	21	& city==.
replace city_plus2=	65	if state2==	22	& city==.
replace city_plus2=	66	if state2==	23	& city==.
replace city_plus2=	67	if state2==	31	& city==.
replace city_plus2=	68	if state2==	32	& city==.
replace city_plus2=	69	if state2==	33	& city==.
replace city_plus2=	70	if state2==	34	& city==.
replace city_plus2=	71	if state2==	35	& city==.
replace city_plus2=	72	if state2==	41	& city==.
replace city_plus2=	73	if state2==	42	& city==.
replace city_plus2=	74	if state2==	43	& city==.
replace city_plus2=	75	if state2==	44	& city==.
replace city_plus2=	76	if state2==	45	& city==.
replace city_plus2=	77	if state2==	46	& city==.
replace city_plus2=	78	if state2==	47	& city==.
replace city_plus2=	79	if state2==	51	& city==.
replace city_plus2=	80	if state2==	52	& city==.
replace city_plus2=	81	if state2==	53	& city==.
replace city_plus2=	82	if state2==	54	& city==.
replace city_plus2=	83	if state2==	55	& city==.
replace city_plus2=	84	if state2==	56	& city==.
replace city_plus2=	85	if state2==	57	& city==.
replace city_plus2=	86	if state2==	58	& city==.
replace city_plus2=	87	if state2==	59	& city==.
replace city_plus2=	88	if state2==	61	& city==.
replace city_plus2=	89	if state2==	62	& city==.
replace city_plus2=	90	if state2==	63	& city==.
replace city_plus2=	91	if state2==	64	& city==.
replace city_plus2=	92	if state2==	71	& city==.
replace city_plus2=	93	if state2==	72	& city==.
replace city_plus2=	94	if state2==	73	& city==.
replace city_plus2=	95	if state2==	74	& city==.
replace city_plus2=	96	if state2==	81	& city==.
replace city_plus2=	97	if state2==	82	& city==.
replace city_plus2=	98	if state2==	83	& city==.
replace city_plus2=	99	if state2==	84	& city==.
replace city_plus2=	100	if state2==	85	& city==.
replace city_plus2=	101	if state2==	86	& city==.
replace city_plus2=	102	if state2==	87	& city==.
replace city_plus2=	103	if state2==	88	& city==.
replace city_plus2=	104	if state2==	91	& city==.
replace city_plus2=	105	if state2==	92	& city==.
replace city_plus2=	106	if state2==	93	& city==.
replace city_plus2=	107	if state2==	94	& city==.
replace city_plus2=	108	if state2== 95	& city==.


*drop state
ren state2 state

label val city_plus city_plus
label val city_plus2 city_plus2
label val state state
label val city city

save ${wd}/cbp/cbp_city_detind_raw.dta, replace

use ${wd}/cbp/cbp_city_detind_raw.dta, clear

// aggregate to city
gcollapse (sum) $cbp_vars, by(year detind city_plus2 )

save ${wd}/cbp/cbp_city_detind.dta, replace

use ${wd}/cbp/cbp_city_detind_raw.dta, clear

// aggregate to state
gcollapse (sum) $cbp_vars, by(year detind state )

save ${wd}/cbp/cbp_state_detind.dta, replace

erase  ${temp}/part1.dta
erase  ${temp}/part2.dta
erase  ${temp}/part3.dta
erase  ${temp}/part4.dta
erase  ${temp}/part5.dta





